from sqlalchemy.orm import Session
from passlib.apps import custom_app_context as pwd_context
from elasticsearch import Elasticsearch

from common import models, schemas,logger

log=logger.tlog()
tlogger=log.define()

"""
模糊检索公司名称，之后反悔mysql的信息，匹配10条
"""
def search_company(db: Session, company: schemas.Company):
    es = Elasticsearch([{'host':'es-cn-0pp1gjl87000am0gj.public.elasticsearch.aliyuncs.com','port':9200}], http_auth=('elastic', '10Nzjzhc!'), timeout=3600)
    secdata=company.company_name
    if company.country_iso_code is not None:
        #带country_iso_code的查询
        countydata=company.country_iso_code.lower()
        res = es.search(index= 'company_name', size= 10, body= {"query":{"bool":{"must":[{"term":{"country_iso_code":countydata}}],"must_not":[],"should":[{"match":{"company_name":secdata}},{"match":{"company_local_name":secdata}}]}},"from":0,"size":10,"sort":[],"aggs":{}})
    else:
        #不带country_iso_code的查询
        res = es.search(index= 'company_name', size= 10, body= {"query":{"bool":{"must":[],"must_not":[],"should":[{"match":{"company_name":secdata}},{"match":{"company_local_name":secdata}}]}},"from":0,"size":10,"sort":[],"aggs":{}})

    #tlogger.debug(res)   
    # 将ES的查询结果的bid生成列表，供后续查询mysql使用
    valueList=[]
    for list1 in res['hits']['hits']:
        str1=list1['_source']
        valueList.append(str1['bid'])
    
    #判断反悔结果是否为空
    if not len(valueList):
        return []
    #tlogger.debug(valueList)
    
    cursor = db.execute('select bid,company_name,company_local_name,country_iso_code,city,address1,address2,address3,address4\
         from tht.contact_information where bid in :bid', params={"bid": valueList})
    
    #fields = ['bid','company_name']
    #cursor = db.execute('select :fields from tht.contact_information where bid =:bid', params={"fields":fields,"bid": 'CN9360812942'})
    res_rows = cursor.fetchall()
    #转换字典
    result = [dict(zip(result.keys(), result)) for result in res_rows]

    return result


def get_baseinfo(db: Session, report: schemas.Report):
    cursor = db.execute('select tid,company_name as \'公司名称(英文)\' ,company_local_name as \'公司名称(本地)\'  ,country_iso_code as \'国家ISO代码\',city as \'城市\',\
        address1 as \'地址1\',address2 as \'地址2\',address3 as \'地址3\',address4 as \'地址4\',\
        latitude as \'维度\',longitude as \'经度\',nuts1 as \'NUTS1 行业代码\',nuts2 as \'NUTS2 行业代码\',nuts3 as \'NUTS3 行业代码\',state_or_province as \'州或省（美国或加拿大）\',\
            county_us_uk as \'县（英美）\',address_type as \'地址类型\'\
         from tht.contact_information where bid = :bid', params={"bid": report.bid})
    #cursor = db.execute('select bid, company_name from tht.contact_information where bid =:bid', params={"bid": 'CN9360812942'})
    res_rows = cursor.fetchall()
    #转换字典
    result = [dict(zip(result.keys(), result)) for result in res_rows]

    return result

def get_size_group_information(db: Session, report: schemas.Report):
    cursor = db.execute('select bid,bvd_major_sector as \'主营产业\',company_category as \'公司分类\',cur_market_cap,main_exchange as \'当前市值\',ldu as \'上市状态\', delisted_date as \'退市日期\',\
        comp_no as \'企业集团的公司数量\',shareholder_no as \'收录到的股东数量\',subsidiaries_no as \'收录到的子公司数量\',\
        branch_no as \'收录分支机构数\',woman_owned as \'女性为企业所有者指数（美国）\',ethnic_min_owned as \'少数族群为企业所有者指数（美国）\'\
         from tht.size_group_information where bid = :bid', params={"bid": report.bid})
    #cursor = db.execute('select bid, company_name from tht.contact_information where bid =:bid', params={"bid": 'CN9360812942'})
    res_rows = cursor.fetchall()
    #转换字典
    result = [dict(zip(result.keys(), result)) for result in res_rows]

    return result